package com.hw01.mapper;

import com.hw01.vo.PunchingCardRecordVo;
import org.apache.ibatis.annotations.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.github.yulichang.base.mapper.MPJJoinMapper;
import com.hw01.entity.PunchingCardRecord;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 打卡记录 Mapper 接口
 * </p>
 *
 * @author zhong
 * @since 2024-03-18
 */
@Mapper
public interface PunchingCardRecordMapper extends BaseMapper<PunchingCardRecord>, MPJJoinMapper<PunchingCardRecord> {

    /**
     * 查询打卡记录
     * 
     * @param userId 员工用户id
     * @param firstDay 月份首日
     * @return 结果
     */
    @Select("<script>" +
            "with recursive v1 as (\n" +
            "    select #{firstDay} as date_value\n" +
            "    union all\n" +
            "    select date_add(date_value, interval 1 day) as date_value\n" +
            "    from v1\n" +
            "    where <![CDATA[ date_value < least(last_day(#{firstDay}), current_date()) ]]>\n" +
            ")\n" +
            "select \n" +
            "      pcr.id\n" +
            "    , t.user_id\n" +
            "    , t.user_name \n" +
            "    , pcr.clock_in\n" +
            "    , pcr.address\n" +
            "    , t.date_value as punch_card_date\n" +
            "    , case when <![CDATA[ cast(t.date_value as date) < cast(t.date_on_board as date) then '未入职' ]]>\n" +
            "          when pcr.id is not null then '正常'\n" +
            "          else '缺卡'\n" +
            "    end as punch_card_state\n" +
            "from (\n" +
            "    select v1.date_value, e.id as user_id, e.user_name, e.date_on_board from v1,employee e \n" +
            "    where e.login_name != 'admin'\n" +
            "      and e.del_state = 0\n" +
            "<if test='userId != null and userId != \"\"'>" +
            "      and e.id = #{userId}\n" +
            "</if>" +
            ") t\n" +
            "left join punching_card_record pcr \n" +
            "       on <![CDATA[ pcr.clock_in >= #{firstDay} ]]>\n" +
            "      <![CDATA[ and pcr.clock_in < date_add(last_day(#{firstDay}), interval 1 day)]]>\n" +
            "      and pcr.user_id = t.user_id \n" +
            "      and cast(date_format(pcr.clock_in, '%y-%m-%d') as date) = cast(t.date_value as date)\n" +
            "order by t.date_value, t.user_id\n" +
            "</script>")
    List<PunchingCardRecordVo> selectPunchingCardRecord(Long userId, String firstDay);

}
